package homeworkjdbc.dao;

import homeworkjdbc.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;

/**
 * Created by Administrator on 2017/9/13.
 */
//操作User表
public class UserDao {
    //查询
    public List<User> findAll() {
        Connection conn=JDBCUtils.getConnection();

        Statement statement=null;
        ResultSet set=null;

        //创建一个集合，添加对象
        ArrayList<User> list=new ArrayList<>();
        try {
            statement = conn.createStatement();
            String sql="select * from user";
            set=statement.executeQuery(sql);

            while (set.next()){
                User user=new User();
                user.setId(set.getInt(1));
                user.setUsername(set.getString(2));
                user.setPassword(set.getString(3));
                user.setEmail(set.getString(4));
                user.setPhone(set.getInt(5));
                list.add(user);
            }
            return list;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(set!=null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                JDBCUtils.close();
            }

        }

        return null;
    }
    //增加
    public boolean insert(User user) {
        Connection conn=JDBCUtils.getConnection();
        PreparedStatement ps =null;
        String sql="insert into user values(?,?,?,?,?)";

        try {
            //1.进行预处理
            ps=conn.prepareStatement(sql);
            //2.设置参数
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getEmail());
            ps.setInt(5,user.getPhone());

            int num=ps.executeUpdate();

            //当返回值为1时正确，为0时错误
            if(num==1){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                JDBCUtils.close();
            }
        }


        return false;
    }
    //修改
    public boolean update(User user){
        Connection conn=JDBCUtils.getConnection();
        PreparedStatement ps=null;
        String sql="update user set username=?,password=? where id=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    //删除
    public boolean delete(int id){
        Connection conn=JDBCUtils.getConnection();
        PreparedStatement ps=null;

        try {
            String sql="delete from user where id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);

            int num=ps.executeUpdate();

            if(num==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                JDBCUtils.close();
            }
        }
        return false;
    }
    //按主键查询
    public User findById(int id){
        Connection conn=JDBCUtils.getConnection();
        //Statement只能用于处理静态的sql语句
        //Statement statement =null;

        //使用预编译的PreparedStatement
        PreparedStatement ps=null;
        ResultSet set=null;

        //User user=new User();
        try {
            //编写sql语句，?是占位符
            String sql="select * from user where id=?";

            //sql语句交给ps进行预处理
            ps=conn.prepareStatement(sql);

            //设置sql语句的参数,1对应第一个问号
            ps.setInt(1,id);
            //ps.setString(2,username);

            //执行查询，不需要传参数
            set=ps.executeQuery();
            //处理结果
            while (set.next()){
                User user=new User();
                user.setId(set.getInt(1));
                user.setUsername(set.getString(2));
                user.setPassword(set.getString(3));
                user.setEmail(set.getString(4));
                user.setPhone(set.getInt(5));
                return user;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(set!=null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                JDBCUtils.close();
            }
        }
        return null;
    }
}
